

ALTER PROC sp_get_all_lots( @processStep_Id VARCHAR(10), @location_ID VARCHAR(10) )
AS
BEGIN
	DECLARE @results TABLE( Lot_ID INT, Lot_Number VARCHAR(50), Lot_Current_Status VARCHAR(50) ) 
	DECLARE @lotID INT
	DECLARE @lotNumber VARCHAR(50)
	DECLARE @lotStatus VARCHAR(50)
	DECLARE @processType VARCHAR(50)
	DECLARE @qtyOut INT
	DECLARE @closeLotCursor CURSOR
	
	-- FETCH THE RUNNING LOTS
	INSERT @results SELECT Lot_ID, Lot_Number, Lot_Current_Status FROM Lot_Table WHERE Lot_Current_Status <> 'CLOSE' AND Lot_Current_Status <> 'MERGE' AND processStep_Id = @processStep_Id AND Location_ID = @location_ID
	
	-- FETCH Close LOTS which are having Qty Out and Lot Status is null
	SET @closeLotCursor = CURSOR FOR SELECT Lot_ID, Lot_Number, Lot_Current_Status, Ps_Type_Name FROM Lot_Table, Process_Step WHERE Lot_Table.ProcessStep_id = Process_Step.ProcessStep_id AND Lot_Current_Status = 'CLOSE' AND Lot_Table.processStep_Id = @processStep_Id AND Location_ID = @location_ID
	OPEN @closeLotCursor
	FETCH NEXT FROM @closeLotCursor INTO @lotID, @lotNumber, @lotStatus, @processType
	WHILE @@FETCH_STATUS  = 0
	BEGIN
		SET @qtyOut = NULL
		IF @processType = 'FAB'
		BEGIN
			SELECT @qtyOut = FL_Output_Quantity FROM FAB_LOT_HISTORY WHERE LOT_ID = @lotId AND FL_Current_Status = 'Close' AND FL_Output_Quantity IS NOT NULL AND FL_Output_Quantity <> 0 ORDER BY TimeStamp DESC
		END
		ELSE IF @processType = 'SORT'
		BEGIN
			SELECT @qtyOut = SL_Die_Qty_Out FROM SORT_Lot_HISTORY WHERE LOT_ID = @lotId AND SL_Current_Status = 'Close' AND SL_Die_Qty_Out IS NOT NULL AND SL_Die_Qty_Out <> 0 ORDER BY TimeStamp DESC
		END
		ELSE IF @processType = 'Assembly'
		BEGIN
			SELECT @qtyOut = AL_Qty_Out FROM ASSEMBLY_Lot_HISTORY WHERE LOT_ID = @lotId AND AL_Current_Status = 'Close' AND AL_Qty_Out IS NOT NULL AND AL_Qty_Out <> 0 ORDER BY TimeStamp DESC
		END
		ELSE IF @processType = 'TEST'
		BEGIN
			SELECT @qtyOut = FTL_Qty_Out FROM FINAL_TEST_LOT_HISTORY WHERE LOT_ID = @lotId AND FTL_Current_Status = 'Close' AND FTL_Qty_Out IS NOT NULL AND FTL_Qty_Out <> 0 ORDER BY TimeStamp DESC
		END
		ELSE
		BEGIN
			SELECT @qtyOut = IL_Qty_Out FROM INVENTORY_LOT_HISTORY WHERE LOT_ID = @lotId AND IL_Current_Status = 'Close' AND IL_Qty_Out IS NOT NULL AND IL_Qty_Out <> 0 ORDER BY TimeStamp DESC
		END

		IF @qtyOut IS NOT NULL AND @qtyOut != 0
		BEGIN
			INSERT INTO @results VALUES ( @lotID, @lotNumber, @lotStatus )
		END

		FETCH NEXT FROM @closeLotCursor INTO @lotID, @lotNumber, @lotStatus, @processType
	END
	CLOSE @closeLotCursor
	DEALLOCATE @closeLotCursor

	SELECT DISTINCT Lot_ID, Lot_Number, Lot_Current_Status FROM @results ORDER BY Lot_ID
END